<?php
include dirname(__FILE__) ."/defactivite.php";

class ElearningExtractorService {
	
	private $xmltabid=array();
	private $_cnn;
	private $_prefix;
	
	
	
	public function __construct(){
		$this->_cnn=mysql_connect("localhost","root","") or die('&lt;error>connexion impossible&lt;/error>');
			
		mysql_select_db("moodlecarthage") or die('&lt;error>Base down&lt;/error>');
	}
	
	public function __destruct(){
		
	}
	
	
	
	/**
	 * nombrecours method
	 *
	 * @param String $idetab
	 *
	 * @return String
	 */
	public function nombrecours($idetab){
		
		
		
		$this->_prefix="mdl_";
		
		if(!empty($idetab)){
			$req =$this->setPrefixInRequest("SELECT
												id as 'idcours',
												fullname as 'namecours', 
												DATE( FROM_UNIXTIME( `startdate` ) ) as 'datedebut', 
												DATE( FROM_UNIXTIME( `timecreated` ) ) as 'datecreationcours',
												DATE( FROM_UNIXTIME( `timemodified` ) ) as 'datemodifcours' 
											FROM 
												{@prefix}course 
											WHERE 
												visible = 1 AND category = '".$idetab."'"
											, 
											$this->_prefix
										   ) ;
			
		}else{
			$req =$this->setPrefixInRequest("SELECT id as 'idcours', fullname as 'namecours' FROM {@prefix}course WHERE visible = 1 ", $this->_prefix) ;
			
		}
		$sql =  mysql_query($req) ;
	
		while( $row = mysql_fetch_array($sql))
		{
		
			$res[]=$row;
				
		}
		return json_encode($res);
	}
	
	//////////////////////////////////////////////////////////////////////////////////////
	/**
	 * etudiantparcours method
	 *
	 * @return String
	 */
	
	public function etudiantparcours()
	{
		$this->_prefix="mdl_";
		
			$req =$this->setPrefixInRequest("SELECT
					c.id, c.fullname, u.firstname, u.lastname, u.deleted
					
					FROM
					{@prefix}course
					JOIN prefix_context AS ctx ON c.id = ctx.instanceid
					JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
					JOIN prefix_user AS u ON u.id = ra.userid
					WHERE
					ra.roleid =5
					AND ctx.instanceid = c.id",
					
					$this->_prefix
			) ;
		
		$sql =  mysql_query($req) ;
	
		while( $row = mysql_fetch_array($sql))
		{
	
			$res[] = $row;
	
		}
		return json_encode($res);
	}
	////////////////////////////////////////////////////////////////////////////
	
	
	
	/**
	 * extractor method
	 *
	 * @param String $xml
	 *
	 * @return String
	 */
	public function extractor($xml){
	 	return $this->extractsql(html_entity_decode($xml));
	}
	
	
	private function extractsql($xml){
		
		$this->_cnn=mysql_connect("localhost","root","") or die('&lt;error>connexion impossible&lt;/error>');
			
		mysql_select_db("moodlecarthage",$this->_cnn) or die('&lt;error>Base down&lt;/error>');
		
		$this->_prefix="mdl_";
		
		$oxml = simplexml_load_string ($xml) ;
		$sqlstr="";
		foreach ($oxml->sql as $sql){
				
		
				$sqlstr="SELECT ".(string)	$sql->SELECT ." FROM ".$sql->From." ".(string)$sql->Jointure;
				if((string) $sql->WHERE !=""){
					$sqlstr.=" WHERE ".(string)$sql->WHERE;
				}
			
				if((string) $sql->GroupBy !=""){
					$sqlstr.=" Group By ".(string)$sql->GroupBy;
				}
				
				if((string) $sql->Having !=""){
					$sqlstr.=" Having ".(string)$sql->Having;
				}
				
				if((string) $sql->OrderBy !=""){
					$sqlstr.=" Order By ".(string)$sql->OrderBy;
				}
				
		}
	  	//return  $sqlstr;
		
		return  json_encode($this->autre($sqlstr));
		
		
	}
	
	private function tomyarray($xml)
	{
		
		//echo "<pre>";
		//print_r($oxml);
		//echo "</pre>";
		//echo $oxml['name'];
		
		$tabres=array();
		
		
		foreach ($oxml->activites->activite as $activite)
			{
				$tabres[(string)$activite['name']]["name"]=(string)$activite['name'];
			
		foreach ($activite->axes->axe as $axe)
			{
				$tabres[(string)$activite['name']]['axes'][]=(string)$axe['name'];
			}
			if(isset(DEF_ACTIVIE::$Acivites[(string)$activite['name']]))
			{
				$this->{(string)$activite['name']}($tabres[(string)$activite['name']]['axes']);
			}
			else
			{
				$tabres[(string)$activite['name']]['erreur']="cette activite n'existe pas";
			}
		}
		
		
		$this->xmltabid=$tabres;
		echo "<pre>";
		print_r($tabres);
		echo "</pre>";	
	}

	private function autre($sql)
	{
		mysql_connect("localhost","root","") or die('&lt;error>connexion impossible&lt;/error>');
			
		mysql_select_db("moodlecarthage") or die('&lt;error>Base down&lt;/error>');
			
		//on envoie la requ�te
		$result = mysql_query($sql) or die('Erreur SQL !<br>'.$sql.'<br>'.mysql_error());
		$table=array();
		
		while( $row = mysql_fetch_array($result))
		{
		
			$table[]=$row;
			
		}
		
		return $table;
	}
	
	private function resultformat($rows){
		
	}
	
	private function XML2Array ( $xml , $recursive = false )
	{
		if ( ! $recursive )
		{
			$array = simplexml_load_string ( $xml ) ;
		}
		else
		{
			$array = $xml ;
		}
	
		$newArray = array () ;
		$array = ( array ) $array ;
		foreach ( $array as $key => $value )
		{
			$value = ( array ) $value ;
			if ( isset ( $value [ 0 ] ) )
			{
				$newArray [ $key ] = trim ( $value [ 0 ] ) ;
			}
			else
			{
				$newArray [ $key ] = $this->XML2Array ( $value , true ) ;
			}
		}
		return $newArray ;
	}
	
	private function setPrefixInRequest($data,$prefix){
		 
		return str_ireplace("{@prefix}", "$prefix", "$data");
		 
	}
	
}

?>